今天介紹 Archive 引擎與 init_connect 參數的配合運用.
在實務上,時常有需要對資料庫的操作留下紀錄.MySQL有提供 binary log,
可以將操作的過程紀錄;或者我們也需要了解有哪些user在哪些時候登入.
MySQL有提供一個 init_connect 參數,我們可以設定SQL Command,
當user登入時執行.這就可以讓我們對user登入作一個紀錄.
以下為範例:

-- 使用 root 登入
-- 建立 Database 來存放資料
CREATE DATABASE myaudit;

-- 建立 Table
USE myaudit;

CREATE TABLE audit_log (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
connection_id INT UNSIGNED NOT NULL,
user_name VARCHAR(50),
define_name VARCHAR(50),
use_schema VARCHAR(50),
login_time TIMESTAMP DEFAULT NOW()
) ENGINE=ARCHIVE;

-- 讓所有的使用者 對 myaudit 都有 INSERT 的權限
INSERT INTO mysql.db(Host, Db, User, Insert_priv)
VALUES ('%', 'myaudit', '', 'Y');

FLUSH PRIVILEGES;

-- 編修 MySQL 的設定檔, 在 [mysqld] section
-- 設定 init_connect

init_connect = "INSERT INTO myaudit.audit_log(connection_id, user_name, define_name, use_schema) VALUES(CONNECTION_ID(), USER(), CURRENT_USER(), SCHEMA());"

-- 重新啟動 MySQL
-- 用其他 user 登入
-- 用 有權限查看 myaudit.audit_log 的 user 檢查 登入紀錄

SELECT *
FROM audit_log;
+----+---------------+-------------------+----------------+------------+---------------------+
| id | connection_id | user_name         | define_name    | use_schema | login_time          |
+----+---------------+-------------------+----------------+------------+---------------------+
|  1 |            48 | test@localhost    | test@localhost | test       | 2012-10-11 14:07:42 |
|  2 |            50 | test@192.168.1.11 | test@192.168.% | NULL       | 2012-10-11 14:08:34 |
+----+---------------+-------------------+----------------+------------+---------------------+

-- 可以看到測試的兩個user分別從不同機器登入的紀錄
-- 但是 root 的登入紀錄是沒有的,因為 init_connect 
-- 是針對沒有 SUPER 權限的user,才會啟動.

-- 接著測試刪除資料
-- 使用 test 登入
CREATE TABLE audit_test(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data CHAR(10)
);

INSERT INTO audit_test(data) VALUES ('test');

DROP TABLE audit_test;

-- 使用 mysqlbinlog 工具程式找出關於 audit_test 的相關操作
$ mysqlbinlog --start-date="2012-10-11 14:00" ./mysql-bin.000075 | grep 'audit_test' -B 5

# at 1667
#121011 14:17:55 server id 1  end_log_pos 1779 	Query	thread_id=52	exec_time=0	error_code=0
SET TIMESTAMP=1349936275/*!*/;
DROP TABLE `audit_test` /* generated by server */

-- 可以看到 thread_id=52
-- 這時候可以用 audit_log 來查看
SELECT *
FROM audit_log
WHERE connection_id = 52
AND login_time > '2012-10-11 14:00';

+----+---------------+----------------+----------------+------------+---------------------+
| id | connection_id | user_name      | define_name    | use_schema | login_time          |
+----+---------------+----------------+----------------+------------+---------------------+
|  3 |            52 | test@localhost | test@localhost | test       | 2012-10-11 14:14:40 |
+----+---------------+----------------+----------------+------------+---------------------+

-- 就是 test 由 localhost 登入這user 下 DROP TABLE audit_test 

近來對資料的紀錄日益重要,以上可提供大家在使用MySQL時,一個紀錄的機制.